Category Archives: SQL

SQL database in suspect or emergency mode

If you have a database that is in suspect or emergency mode do this to get it online:

ALTER DATABASE XXX SET ONLINE

If this work is’s all fine, else you have to kill all active connections with this:

select
t1.session_id,
t1.start_time as request_start_time,
db_name(t1.database_id) as DatabaseName,
t1.reads,
t1.writes,
t1.logical_reads,
t1.logical_reads*8/1024/1024 as logical_reads_GB,
t1.cpu_time as cpu,
t1.total_elapsed_time,
t1.wait_time,
t1.wait_type,
t1.wait_resource,
t1.open_transaction_count,
t1.blocking_session_id,
SUBSTRING(text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text,
text as full_statement_text,
login_time,
program_name,
login_name,
client_net_address,
query_plan
from
sys.dm_exec_requests t1
outer apply sys.dm_exec_sql_text(sql_handle) t2
outer apply sys.dm_exec_query_plan(t1.plan_handle)
inner join sys.dm_exec_sessions t3 on t1.session_id = t3.session_id
inner join sys.dm_exec_connections t4 on t1.session_id = t4.session_id
where t1.session_id > 50 and t1.session_id <> @@spid

When all connections is terminatet do this:

ALTER DATABASE XXX SET EMERGENCY
ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (XXX, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE XXX SET ONLINE
ALTER DATABASE XXX SET MULTI_USER

To see the status on this you this command:

SELECT
session_id ,
request_id ,
percent_complete ,
estimated_completion_time ,
DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,
start_time ,
status ,
command
FROM sys.dm_exec_requests

 

Change SQL max memory

Log on the SQL Server and start a new Query

– Set the SQL Server in Operation Mode –

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
go

– View Settings that are running on the server –

EXEC sp_configure;

– Set Max Memory (MB) – (in this example the max is set to 6 GB)

EXEC  sp_configure'max server memory (MB)',6000;

Activate the Settings

RECONFIGURE;
GO

Get SQL version

SELECT ‘SQL Server ‘
+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘
+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘
+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’

Use Windows Login with Navision

If you want to use your windows login as login for Navision you have to do this:

1. Copy the xp_ndo.dll file to the location you want store it. The file can be found under “D:\SQLDatabase\PFiles\Microsoft Dynamics NAV\60\Database” on the product DVD.
2. Run the following SQL command to add the stored procedures.

USE master
EXEC sp_addextendedproc xp_ndo_enumusersids, ‘C:\[Location of file]\xp_ndo.dll’
GO

GRANT EXECUTE
ON [xp_ndo_enumusersids]
TO PUBLIC
GO

USE master
EXEC sp_addextendedproc xp_ndo_enumusergroups, ‘C:\ [Location of file]\xp_ndo.dll’
GO

GRANT EXECUTE
ON [xp_ndo_enumusergroups]
TO PUBLIC
GO

SQL update users

The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login Mary does not exist.

USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Mary’, NULL, ‘B3r12-3x$098f6’;
GO

Examble:

USE Database;
GO
EXEC sp_change_users_login ‘Auto_Fix’, %User&, NULL, %password%;
GO

read more here : http://msdn.microsoft.com/en-us/library/ms174378.aspx