Truncate a SQL Server log file (Reduce the size of an LDF file)

I have been doing database administration and have been asked several times to reduce the size of database files. The actual mdf file is small, 3MB, but the LDF file is 10GB !!
I have read about the DBCC SHRINKFILE command and tried this, but the file has stayed at 10GB even though it said the command executed fine.
I've also tried using the wizard in SQL Server to reduce the LDF to a specified file size (800MB), this also failed to change the size even though it gave me the impression it had worked and was successful via the wizard.

The best thing I found is to change the recovery mode of database to RECOVERY SIMPLE before executing the SHRINKFILE command. After that change back the recovery mode to FULL.


ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL

Happy Coding :)

Comments

Call User-defined Function on Linked Server :SQL Server

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a "four-part naming" convention (server.database.dbo.Function), you may receive error message. The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. Thats why error message indicates that the syntax of a Transact-SQL statement is incorrect. To work around this problem, use the Openquery function instead of the four-part naming convention. For example, instead of the following query Select * from Linked_Server.database.dbo.Function(10) run a query with the Openquery function: Select * from Openquery(Linked_Server,'select database.dbo.Function(10)') If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. For example: exec Linked_Server.database.dbo.sp_executesql N'SELECT database.dbo.Function(@input)',N'@input

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL. I get the following message: Unable to open physical file "C:\ArrowSQL\Arr@Data\Arrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)". I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file. The machine runs Windows Vista Business. SQL has SP 2 loaded. Use the below script to find the sql service account Code Snippet declare @sqlser varchar ( 20 ) EXEC master .. xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' , @key = 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER' , @value_name = 'objectname' , @value = @sqlser OUTPUT PRINT 'Account Starting SQL Server Service:' + convert ( varchar ( 30 ), @sqlser ) After getting the servi

Configuring CORS in IIS - Response to preflight request doesn't pass access control check: It does not have HTTP ok status

The Access-Control-Allow-Origin Header Explained – With a CORS Example Often times when calling an API, you may see an error in your console that looks like this: Access to fetch at 'http://somesite.com' from origin 'http://yoursite.com' has been blocked by CORS policy: The 'Access-Control-Allow-Origin' header has a value that is not equal to the supplied origin Add following in <system.webServer> <httpProtocol> <customHeaders> <add name="Access-Control-Allow-Methods" value="*" /> <add name="Access-Control-Allow-Origin" value="*" /> <add name="Access-Control-Allow-Headers" value="*" /> </customHeaders> </system.webServer> After adding the above code in web.config, received the following error in response. .... Has been blocked by CORS policy: Response to preflight request doesn’t pass access control check:

Developers Mania

Search This Blog