select log_reuse_wait_desc from sys.databases where name = 'AdventureWorks' If Output is "LOG_BACKUP" -- Execute 2 Times just in case -- BACKUP LOG AdventureWorks TO DISK='NUL:' -- Now Truncate the Log -- USE AdventureWorks GO ALTER DATABASE AdventureWorksSET RECOVERY SIMPLE WITH NO_WAIT; GO DBCC SHRINKFILE(AdventureWorks_LOG, 100); GO ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT; GO
sql-server
Delete Duplicate Rows SQL Server
1. Add an Identity Column to the Table alter table MASTER add identity_col int identity(1,1); 2. Delete duplicate data using Below Query (In the GROUP BY CLAUSE you can specify multiple columns if the row is indetified using more than one field) delete from MASTER where identity_col not in ( select Min(identity_col) from MASTER group … Continue reading Delete Duplicate Rows SQL Server
Enable XP_CMDSHELL SQL Server 2008 2012
EXEC sp_configure 'show advanced option',1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
Run Powershell Script from Task Scheduler
Action : Start a Program Program : powershell.exe Add Arguements (Optional) "& 'c:\checkDS\CheckDiskSpace_post.ps1'"
Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.
SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clause "with schemabinding" CREATE VIEW [dbo].[ViewName] with schemabinding AS SELECT id, activityID, userID, value, date FROM dbo.TableName WHERE (field = 'activityID') Now you … Continue reading Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.
Create Indexed Views SQL Server 2005 and 2008
SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clausa "with schemabinding" Now go ahead amd create the index on the view you just created.
Sql server 2008 database mirroring manual failover
In case your mirroring has been suspended and you have tried everything else and nothing works. Your last option is to break the mirroring and restore the mirror database and connect the client application to the mirror site. Before proceeding with this please note that doing this will require you to rebuild the Data Mirroring … Continue reading Sql server 2008 database mirroring manual failover
The mirror database has insufficient transaction log data
After you have restored the mirror database and configured Data mirroring, when you start the mirroring it throws this error. The solution to resolving this is to restore the backup of the transaction log to the Mirror database. 1. Take backup of the TLOG at Principal site BACKUP LOG TO DISK = N'E:\transaction_log_bkp\Tlog_Bkp.trn' WITH NOFORMAT, … Continue reading The mirror database has insufficient transaction log data
TRANSFER SQL SERVER LOGINS BETWEEN 2 DIFFERENT SERVERS
1. RUN BELOW SCRIPT ON SOURCE SERVER 2. RUN BELOW COMMAN ON SOURCE SERVER TO CREATE LOGIN SCRIPTS 3. APPLY THE LOGIN SCRIPTS ON DESTINATION DATABASE FOR EG:
Delete Large Number of Rows SQL Server
To delete rows without locking a table which has say more than 100 million rows to be deleted. Or a large table with archived data has to be deleted. You can use the below function to do this deletion.
SimpleSQLDBA | Shadab Mohammad 