To get the version of SQL Server, run the following: c:\> osql /E /S .\INSTANCE_NAME -Q "select @@version"
String of thoughts on science, technology and programming
Posts
Observed this on Microsoft SQL Server 2014 (SP2) but is probably true for any SQL Server versions. I am seeing a lot of lines like below in ERRORLOG: 2020-09-11 09:45:00.87 spid20s Starting up database 'DatabaseName'. The reason for this repeated logs is that the AUTOCLOSE property of the database is set to TRUE. When AUTOCLOSE is set once the last connection to the database closes it will also close the database and release all the resources associated with it. This can be useful to conserve precious resources on the server, unfortunately this can also impact performance and I have seen cases that it actually depletes the resources on the server when the server is under memory pressure combined with maximum server memory is reached. To disable AUTOCLOSE , open database properties using SSMS and set Auto Close to False under Options page. Reference: https://blog.sqlauthority.com/2016/02/20/sql-server-starting-up-database-why-multiple-times-in-errorlog/
Modifying an SQL Server table that requires the table to be re-created is not allowed by default using SQL Server Management Studio for good reason. For example, changing a column of type nvarchar from 10 to 20 will result with the following error: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created. To allow schema change for the above situation using SSMS, do the following: Open SSMS (SQL Server Management Studio). I am using SSMS 2014. For good measure, back-up the database. Navigate to Tools | Options . Click on Designers . Remove the check box on Prevent saving changes that require table re-creation . Click on OK . Proceed with changing the table schema. Again for good measure, enable Prevent saving changes that require table re-crea
c:\> sqlcmd -S .\Db_INSTANCE -Q "DROP DATABASE dbname" Where: Db_INSTANCE is the name of SQL Server instance dbname is the name of the database to drop Reference(s): http://windows-linux-admin.blogspot.com/2011/06/deleting-database-with-sqlcmd.html
This may apply to other SQL Server 2005 products but since I haven't not tried them myself, I can't definitely say that this is the case. Anyway, on default SQL Server 2005 Express install it will create one sql server instance named "SQLEXPRESS." For most occasions this should do just fine but in some cases a named/new instance is necessary to partition/compartmentalize the development of an application. Creating a new sql server instance is actually very easy. To create a new instance, do the following: a) Secure a copy of SQL 2005 Express installation media. b) Run setup.exe. c) Accept license agreement. After accepting the agreement, the installation program will perform various other checks like disk space available... etc. d) "Welcome to the Microsoft SQL Server Installation Wizard" just lazily hit on "Next" button. e) In System Configuration Check screen, if the reports are okay then proceed, hit on "Next" button again. f)
c:\> sqlcmd -S .\DB_INSTANCE -Q "exec sp_databases" Reference(s): http://windows-linux-admin.blogspot.com/2011/06/list-all-databases-with-sqlcmd.html