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: SQL Server
Upgrading to SQL Server 2017 – VS Shell installation has failed with exit code 1638
I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people. The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 Redistributables, which it can’t because the SQL Server installation doesn’t allow you to install an older version with the newer version installed. This Microsoft Support article presents their explanation of the problem, and their recommended workarounds. Solution 1: Install SQL Server …
Query to list number of records in each table in a database
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: The transaction log for database is full
Right-click your database in SQL Server Manager, and check the Options page. Switch Recovery Model from Full to Simple then right-click the database again. Select Tasks Shrink, Files. Shrink the log file to a proper size (I generally stick to 20-25% of the size of the data files). Switch back to Full Recovery Model, and take a full database backup straight away. You now need to figure out whether you need the ability to recover your database to a point-in-time. If so, schedule regular transaction log and perhaps differential backups according to the requirements of your business. In case the …
Query to list number of records in each table in a database
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 …
SQL Server, Common Table Expressions
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE can be used to: Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions. Substitute for a …
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 …