Database

Truncate log on SQL 2008

Some times old tricks just dies, thats what happend with "BACKUP LOG WITH TRUNCATE_ONLY"
When you run that on a SQL 2008 server you will read: "truncate_only’ is not a recognized BACKUP option"
It´s gone deprecated because of misuse (what?), anywhay this the new style of doing the same thing wich is to clear the logfile.
 
USE dbname;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  — here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
— Reset the database recovery model.
ALTER DATABASE dbname
SET RECOVERY FULL;
GO

MS SQL 2008 Express a good start (pay when you really need it)

Express is the name of Microsofts “free” tools, to met the competition with the open source alternatives. I realy like SQL 2008 Express it is basically the full (and very expensive) version with som limitiations, wich are:

  • CPUs: Only 1 CPU. If a system has more than 1 SQL Express 2008 will still run but limit itself to 1 CPU.
  • RAM: 1 GB. More RAM can exist, but again SQL Express 2008 will only make use of a maximum 1 GB.
  • Database Size: 4 GB. This limitation provides for the storage of a considerable amount of data while protecting the domain of the higher-end SQL Server versions.
  • Many solution could live with this limitations it´s saves a lot of license money, and when the day comes there is no problem to change to MS SQL Server 2008, just install it and restore a backup file (it´s no differance between the database files).

    MS SQL 2008 Store Blobs with FILESTREAM

    Today I digged in myself in the new MS SQL function Filestream that was shipped with MS Sql 2008. The problem this function is trying to solve is to store images and other binary files in the database preserving speed and security. To get the maximum performance many developers stores files in the NTFS filesystem and just the path in the database, drawback is that these files dont get backed up along with an ordinary databasebackup. You can restore the paths to the files but the files are gone if the disked has crashed. You could of course backup the files also, but then you have two backup plans to maintain and worrying about. The other solution was to store the files as BLOB objects in the database, MS SQL is´nt realy built for these kind of storage it has a performance hit, here you can se som comparsion of the methods http://msdn.microsoft.com/en-us/library/cc949109.aspx

    Now with Filestream you could use a combinatin of SQL and NTFS storage, thats what it´s all about a filestorage handled by the MS SQL Server engine, when you do a Database backup the files also are backed up I dont go in to details just google around and read about it. When you are ready for try this out I have made a very simple Sample project feel free to download it, I have ripped most of the code from Guy Bersteins site (links below)

    My sample project is here: http://cid-e0d0e097982463fd.skydrive.live.com/self.aspx/CodeSamples/MsSqlFileStreamSample.zip

    Some configuratin is needed for your MS SQL 2008 databse (Filestream must be enabled), read more about this here: http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/09/sql-server-2008-filestream-part-1.aspx