The log file for database is full. Back up the transaction log for the database to free up some log space

While dealing with sql server databases, you might encounter this error while trying to insert data into the database

"The log file for database is full. Back up the transaction log for the database to free up some log space."

As the error message specified you should backup the transaction log in order to free up some log space.

Here are 2 commands that you can use to do just that.

To empty it 

backup log <dbname> with truncate_only

To backup to some other disk

backup log <dbname> to disk='c:\somefile.bak'

Read more...

Device activation error on SQL server

Sometime when you're trying to restore a sql server database from a different server using SQL management studio, you'll get this error

Device activation error 

This is usually caused by  the original database which is in the original server, which may consist of several data files (.mdf), requires the same file location in the new server.

However, this can be resolved by running this command in the SQL management studio.

Because the original database, which may consists of several data files(.ndf, .mdf and .ldf files), requires that the SQL Server B has exact the same file locations

First check total files in the backup by using this query

RESTORE FILELISTONLY FROM DISK='F:\SQLbackup\myDatabase.BAK'

Based on the result, run the following query

RESTORE database SelfService2 FROM disk='F:\SQLbackup\myDatabase.BAK'

with replace,

move 'myDatabase_Data' to 'F:\SQLData\myDatabase_Data.mdf',

move 'myDatabase_Log' to 'F:\SQLData\myDatabase_Log.mdf'

Make sure the new path of database with it name does not exist in that particular path 

Read more...