Microsoft SQL Server

Kill all processes for specific database on Microsoft SQL Server

Problem: How to kill all processes on Microsoft SQL Server database ? Solution: Applied to: Microsoft SQL Server Execute the follow query on master database and change ‘MyDB’ with your DB: DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), spid) + ‘;’ FROM master..sysprocesses WHERE dbid = db_id(‘MyDB’) EXEC(@kill); Best Regards, …

Kill all processes for specific database on Microsoft SQL ServerLeggi altro »

Reset AutoIncrement field in SQL Server 2008 R2 / 2013

Solution: Applied to: Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2012 (but it’s similar for the other). To reset auto incremental field in SQL Server you have to execute the following query: DBCC CHECKIDENT (mytable, RESEED, 0) Best Regards, ITquadro – specialized Blog ITquadro consulenti informatici specializzati in ambienti microsoft sql server. Ci …

Reset AutoIncrement field in SQL Server 2008 R2 / 2013Leggi altro »

How to shrink transaction log on mirror database (Microsoft SQL Server).

Solution: Applied to: Microsoft SQL Server 2008 R2 (but it’s similar for the other). To shrink transaction log manually on mirror database (Microsoft SQL Server) you have to follow these steps: Verify mirroring database: database must be in Principal, Synchronized state. Execute query: USE Databasename BACKUP LOG Databasename TO DISK = N’D:\Backup\DatabaseName_log.bak’ GO DBCC SHRINKFILE(‘DatabaseLogicalName_Log’, 1) …

How to shrink transaction log on mirror database (Microsoft SQL Server).Leggi altro »

Bring online database (access denied) Microsoft SQL Server 2012 BI – Accesso negato mettendo online un database

Problem: If you try to bring online a database and you receive “access denied” probably it’s possible, if you are an administrator with grant rights, that .mdf file doesn’t have right permissions. In my case the problem was NT Service/MSSQLSERVER or NT Service/MSSQLSERVER$IstanceName Solution: right click on .mdf file –> Properties –> Security — Edit –> …

Bring online database (access denied) Microsoft SQL Server 2012 BI – Accesso negato mettendo online un databaseLeggi altro »

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Problem: When you try to delete user from database through Microsoft SQL Server Management Studio you might stumble in the follow error: “Drop failed for User ‘user’. (Microsoft.SqlServer.Smo).” “The database principal owns a schema in the database, and cannott be dropped” In simple terms, the user you want to delete has the property of one or more schema. …

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)Leggi altro »

How remove the SQL Server 2005 Express Tools – Install SQL Server 2008 R2

Problem: When you try to install Microsoft SQL Server 2008 R2 (you are sure that you remove SQL Server 2005 Express Tools from control panel) you can be faced with this anyway:   Solution: Applied to: Windows 7. Click the Windows “Start” button and select “Run” In the text box, enter “regedit” and click “OK” …

How remove the SQL Server 2005 Express Tools – Install SQL Server 2008 R2Leggi altro »