Truncate Logfile in SQL Server

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

Using the code

Step 1. Copy/type the below given SQL.

Step 2. Change @DBName to < Database Name>, @DBName_log to

Step 3. Execute the SQL.

ALTER DATABASE @DBName SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(@DBName_log, 1)
ALTER DATABASE @DBName SET RECOVERY FULL WITH NO_WAIT
GO  

In SQL Server data is stored using two physical files: 

  1. (.mdf)  extension  which contains the data. 
  2. (.ldf) extension which contains log.

Log file size increases very rapidly and depend on the operation performed on the data. After a long time period this file becomes too large. When log file is too large it takes time to perform some operations like ( attach , de-attach, backup, restore … etc ). 

SQL SERVER – Enable xp_cmdshell using sp_configure

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

They good old backdoor called xp_cmdshell is – and that’s absolutely ok – disabled by default starting Microsoft SQL Server 2000+. To re-enable that feature, there’s a stored procedure available called sp_configure. Logo_Ms_SQL

Continue reading

SQL Server 2008: Error Saving changes is not permitted

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

If you have been playing with SQL Server Management Studio 2008 and are modifying tables in the designer, you may run into this error:

Save
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Logo_Ms_SQL

Continue reading

How to: Change the Default Login Authentication Mode to SQL

How to Change the Default Login Authentication Mode to SQL

short description:
use SECURITYMODE=SQL as command line setup parameter when installing SQL Server
or
change one of the following registry keys:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\ Instance Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated security. (Integrated is the default setup for the SQL Server 2000 Data Engine.)

for more refer to article MS Q285097

Solution: SQL Server Enterprise Manager General OLE Error 16386

Tried to start Microsoft SQL Server Enterprise Manager and got stuck at the error message General OLE Error 16386:

try this, maybe this solves the issue..

a. Stop all SQL Server services (MSSQLServer, SQLServerAgent, MSSEARCH)
b. Open command prompt and go to the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn directory
c. Unregister the following .dll files (Ex. regsvr32 /u dtsui.dll): dtsui.dll,sqlns.dll, sqldmo.dll, sqllex.dll, sqlmmc.dll
d. Copy and paste the .dll files from a CD (under x86\Binn directory) to the Program Files\Microsoft SQL Server\80\Tools\Binn
e. Register the .dll files (Ex. regsvr32 sqldmo.dll)