Posts

Altering column name in MS SQL Server using sp_rename

Hi there,  Hope you are doing well.  Below is the script to rename the column in a table in MS SQL server using the sp_rename stored proc. EXEC sp_rename 'TableNameIncludingSchemaName.oldColumnName', 'NewColumnName', 'COLUMN';  Example:- EXEC sp_rename 'dbo.Employee.FName', 'FirstName', 'COLUMN';  FName is the current/existing column name and the FirstName is the new/desired column name.
Post a Comment
Read more

Delete the data from the table without growing log file size

Hi there,  Hope you are doing well.  I have recently encountered a situation where the Drive on the SQL server was getting full. I needed to delete the data from one of the tables by not growing the log file size on the drive. Below is the script that I have ran to delete the data and taking the log backup so that the log file size doesn't grow. Change your database name, table name and the condition in the delete statement where clause. SET NOCOUNT ON; DECLARE @noOfRowsDeleted INT,@noOfRowsToBeDeletedInChunk INT; SET @noOfRowsDeleted = 1; SET @noOfRowsToBeDeletedInChunk = 500; --Here I would like to delete 500 records in every execution WHILE @noOfRowsDeleted > 0 BEGIN   BEGIN TRANSACTION; delete top (@noOfRowsToBeDeletedInChunk) from [YourTableName] where  [Your Condition goes here]   SET @noOfRowsDeleted = @@ROWCOUNT;   COMMIT TRANSACTION;  BACKUP LOG [YourDatabaseName] TO  DISK = N'NUL' WITH NOFORMAT, NOINIT,  NAME = N'YourDatabaseName-Full Database Backup',
Post a Comment
Read more