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 »

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 »

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 »

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 »

Solution: USE master ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE Well done! Best Regards, ITquadro – specialized Blog ITquadro sistemisti specializzati su Bologna ed Emilia-Romagna esperti in installazione e configurazione di Microsoft SQL Server  2012 / 2008 R2 e troubleshooting, ecc.

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 »