This should help reduce the log file size and free up space. If there are pending transactions or the log is in FULL mode, the log should be backed up before performing shrink. Stop backup log: SELECT session_id, command, status, start_time FROM sys.dm_exec_requests WHERE command = ‘BACKUP DATABASE’ OR command = ‘BACKUP LOG’; KILL sesionid;
Categoria: Transact-SQL
How to get different SQL Server date formats
Below is a list of formats and an example of the output. The date used for all of these examples is “2006-12-30 00:38:54.840”. DATE ONLY FORMATS Format # Query Sample 1 select convert(varchar, getdate(), 1) 12/30/06 2 select convert(varchar, getdate(), 2) 06.12.30 3 select convert(varchar, getdate(), 3) 30/12/06 4 select convert(varchar, getdate(), 4) 30.12.06 5 select convert(varchar, getdate(), 5) 30-12-06 6 select convert(varchar, getdate(), 6) 30 Dec 06 7 select convert(varchar, getdate(), 7) Dec 30, 06 10 select convert(varchar, getdate(), 10) 12-30-06 11 select convert(varchar, getdate(), 11) 06/12/30 12 select convert(varchar, getdate(), 12) 061230 23 select convert(varchar, getdate(), 23) 2006-12-30 …
DBCC SHRINKFILE (Transact-SQL)
A. Shrinking a data file to a specified target size The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB. B. Shrinking a log file to a specified target size The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE. C. Truncating a data file The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried …
SQL SERVER – FIX : Error 15023: User already exists in current database.
Error 15023: User already exists in current database. 1) This is the best Solution. First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan. Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. Run following T-SQL Query in …
Recursive Queries using Common Table Expressions (CTE) in SQL Server
Given the example , hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. Syntax Sample (from Root nodes to Leaf noes) Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use. !Importante …
get a substring in SQL SERVER using space as separator
get a substring in SQL SERVER using space as separator source: http://www.dreamincode.net/forums/topic/239440-how-can-i-get-a-substring-in-sql-server-using-space-as-separator/ CHARINDEX (Transact-SQL) http://msdn.microsoft.com/pt-pt/library/ms186323.aspx SUBSTRING (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms187748.aspx
SQL Server Dumper
SQL Server Dumper enables you to dump selected SQL Server database tables into SQL INSERT statements, that are saved as local .sql files and contain all the data required to create a duplicate table, or to be used for backup purposes. You can choose to create an individual .sql file for each table, or combine all selected tables into a single file. This program implements the following special features: Foreign Keys order tables in text file in order to insert data without colisions/errors Primary Key IDENTITY guarantees the value of the IDENTITY field Reference to itself eliminates the constraint and …
SQL SERVER – Insert Data From One Table to Another Table
INSERT INTO SELECT – SELECT INTO TABLE Following three questions are many time asked on this blog. How to insert data from one table to another table efficiently? How to insert data from one table using where condition to anther table? How can I stop using cursor to move data from one table to another table? There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as …
SQL to Select a random row from a database table
Select a random row with MySQL: SELECT column FROM table ORDER BY RAND() LIMIT 1 Select a random row with PostgreSQL: SELECT column FROM table ORDER BY RANDOM() LIMIT 1 Select a random row with Microsoft SQL Server: SELECT TOP 1 column FROM table ORDER BY NEWID() Select a random row with IBM DB2 SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY Select a random record with Oracle: SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1 source link: http://www.petefreitag.com/item/466.cfm
Insert Multiple Rows in SQL Server
In SQL Server 2005, in order to insert 3 rows to a table, you had to run 3 INSERT statements: insert into Customers (Name, City, Phone) values (‘Customer #1’, ‘Jerusalem’, ‘2343245’) insert into Customers (Name, City, Phone) values (‘Customer #2’, ‘Tel Aviv’, ‘0987345’) insert into Customers (Name, City, Phone) values (‘Customer #3’, ‘Haifa’, ‘275466’) In SQL Server 2008, you can insert multiple rows in a single insert statement that takes a number of value arrays: insert into Customers (Name, City, Phone) values (‘Customer #1’, ‘Jerusalem’, ‘2343245’), (‘Customer #2’, ‘Tel Aviv’, ‘0987345’), (‘Customer #3’, ‘Haifa’, ‘275466’) Source link