Configuration Manager; nothing under SQL Server Services

  • Honestly, I give up. I can't connect to this database as sa ('a network-related or instance-specific error occurred error 40 [etc.]') so I open up Configuration Manager, click on SQL Server Services, and... it's completely empty (see attached).

    Why would this happen?

    I'm connecting via remote desktop, and I'm an administrator on that machine (Windows Server 2008 R2). I've tried Google. The SQL Server service must be up and running because people are using that database right now.

    As before if you've read any of my posts, I'm fairly new here, everybody else has left and I know nothing about how this machine was set up. This job sucks. Any pointers would be gratefully received! Thanks.

  • The SQL Server service must be up and running because people are using that database right now.

    Just because people are connecting to a db does not mean that the db instance is where you think it is. If people are connecting to a database that you are trying to maintain/troubleshoot through an application you need to find where the connection string in the application is. It should tell you the instance name where the database is located.

    It is also possible that SQL Server is installed on the server you are trying to access yet you are just not seeing the services in SQL Server Configuration Manager. For example in your screenshot you are running SQL Server Configuration Manager that is from SQL Server 2008/2008 R2. If a newer instance of SQL Server is installed, like SQL Server 2012 it will not be able to see those newer services. Go to Control Panel > Services and see if any SQL Server services are listed.

    Joie Andrew
    "Since 1982"

  • Thanks for the reply. There are no newer instances. The service is running, although it took a while to find because it was given a very unusual name for some reason.

    Is there anything I can do to enable me to see the missing services in Configuration Manager?

  • Unusual name? That raises a red flag.

    Go to Start > Run > Services.msc

    and post the full name of the service running SQL

    Another option... if you are sure you have the correct box, you should be able to open Command Prompt and use sqlcmd -E

    thanks

  • Thanks for the help!

    Ok, it's called [Application name] 5.5 Server - [Application name], and under log on as it has .\[a username related to application name]

    sqlcmd -E returns:

    "Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Miscrosft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections."

    Ironically that's exactly what I can't do :-D. I'm just going to Google it.

    Exactly the same thing happens whether I'm logged in as me (local administrator) or that username mentioned above.

  • There is something else you can try. Can you go to the directory that the SQL binaries are installed (normally something like c:\program files\microsoft sql server\MSSQL10_50.<instancename>\MSSQL), go to the log directory and there should be a series of files labelled ERRORLOG*. Can you post a copy of the ERRORLOG file (the one with no number at the end)? If it has the startup info we should be able to tell you how to connect.

    Joie Andrew
    "Since 1982"

  • I can't find anything like that at all; the menu structure looks very strange. I think I might have to get the contractor for the application in question to have a look at it when he's next in in a couple of weeks. It's a complete shambles.

    Thanks for the help, everybody. If I'd had any idea what I'd be taking on I wouldn't have touched this job with a bargepole ;-).

  • If you can't see a service called "SQL server..." something, it means you may not even have SQL installed there anymore... Or you are using express version.

    Last shot... You mention that is running under ./username. That means that someone created a local username on that server and its running those services. Do you have the password? If you do, you can logging to windows and try again connecting using ssms.

  • Good Day to all expert of SQL, I'm a newbie here, actually I'm not a geek or computer expert, our office has using something program to log something and trace up their updates something like that, as a assistant they put me here to operate it, they also trained me to how to maneuver this application, but suddenly just a few days I encounter this mess, I want to log-in and the sql server does not participate, I search from Google and Luckily I got into it, but It was only for a few days and then this is now the rotten days, I already got many ideas from net but it always same way...

    before from the SSCM I can Run the tree things there but lately the two of it does not work any more, (localSystem)

    [SQL Server (MSSQLSERVER) and SQL Server Agent (MSSQLSERVER)]

    their state is already Stopped

    is there any Ideas Can I do to fix this... 😥

    I want to upload my log error from SQL but it seems my Internet connection is very low. I try to update this tomorrow as I got free time to jump again here

  • Beatrix Kiddo (7/3/2013)


    I can't find anything like that at all; the menu structure looks very strange. I think I might have to get the contractor for the application in question to have a look at it when he's next in in a couple of weeks. It's a complete shambles.

    Thanks for the help, everybody. If I'd had any idea what I'd be taking on I wouldn't have touched this job with a bargepole ;-).

    It sounds like the server you are on is the application server that connects to the SQL server that is on another machine. I would call the vendor for the application and let them know you need the 'connection string for the SQL server', they will know exactly what you are talking about and point you in the right DIR. It's probably in the webconfig file which would be on the C:\.

    MCSE SQL Server 2012\2014\2016

  • Hi- this is a really old thread that has been bumped. Cringe! They actually ended up trashing and rebuilding that server because it was just all wrong. It was definitely meant to have SQL Server on it.

    I didn't update because I didn't have a satisfactory answer that might help anybody in the future; it was just one of those things. Thanks for the replies anyway though.

  • Find below script for SQL server configuration and DB details.

    --***********1. SQL Server version deatils**********

    SELECT

    SERVERPROPERTY('servername') AS [Server Name],@@ServiceName as [InstanceName],

    CASE

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'

    ELSE 'unknown'

    END AS Version,

    SERVERPROPERTY('ProductVersion') AS Number,

    SERVERPROPERTY('Edition') AS Edition,

    SERVERPROPERTY('ProductLevel') AS SP

    --******************2. Database details*************

    Use master

    Go

    select name as [Database Names] from sys.databases

    --******************3. Job Details*************

    GO

    use msdb

    go

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE active_start_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),active_start_date),1,4) + '/' +

    substring(convert(varchar(15),active_start_date),5,2) + '/' +

    substring(convert(varchar(15),active_start_date),7,2)

    END,

    'Start Time' = CASE len(active_start_time)

    WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(active_start_time,3),1)

    +':' + right(active_start_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(active_start_time,5),1)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(active_start_time,6),2)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    END,

    -- active_start_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    +     CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER JOIN dbo.sysjobschedules

    ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time = 0

    UNION

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE next_run_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),next_run_date),1,4) + '/' +

    substring(convert(varchar(15),next_run_date),5,2) + '/' +

    substring(convert(varchar(15),next_run_date),7,2)

    END,

    'Start Time' = CASE len(next_run_time)

    WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(next_run_time,3),1)

    +':' + right(next_run_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(next_run_time,6),2)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    END,

    -- next_run_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    +     CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time <> 0

    ORDER BY [Start Date],[Start Time]

     

    GO

    --******************4. Login details*************

    use master

    GO

    SELECT name AS Login

    FROM sys.server_principals

    WHERE TYPE IN ('U', 'S', 'G')

    and name not like '%##%'

    ORDER BY name, type_desc

     

    --******************5.Min & Max SQL server Memory details*************

    SELECT name, value, value_in_use, [description]

    FROM sys.configurations

    WHERE name like '%server memory%'

    ORDER BY name OPTION (RECOMPILE);

    --******************6. Disk space details*************

    SELECT distinct(volume_mount_point) as Drive,

    total_bytes/1048576 as Size_in_MB,

    available_bytes/1048576 as Free_in_MB,

    (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage

    FROM sys.master_files AS f CROSS APPLY

    sys.dm_os_volume_stats(f.database_id, f.file_id)

    group by volume_mount_point, total_bytes/1048576,

    available_bytes/1048576 order by 1

    --******************7. Database Count*************

    select count(*) as [Database Count] from sys.databases;

    --******************8. Mdf and Ldf file name,size and location*************

    CREATE TABLE #databases

    (

    DATABASE_NAME VARCHAR(50),

    DATABASE_SIZE FLOAT,

    REMARKS VARCHAR(100)

    )

    INSERT #Databases EXEC ('EXEC sp_databases');

    SELECT @@SERVERNAME AS SERVER_NAME,

    DATABASE_NAME,

    SYSMFM.source_file_name_main,

    SYSMFM.physical_name_main,

    SYSMFL.source_file_name_log,

    SYSMFL.physical_name_log,

    DATABASE_SIZE AS '(KB)',

    ROUND(DATABASE_SIZE / 1024, 2) AS '(MB)',

    ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS '(GB)',

    SYSDB.compatibility_level,

    SYSDB.create_date,

    SYSDB.database_id,

    SYSDB.collation_name,

    SYSDB.recovery_model,

    SYSDB.recovery_model_desc,

    SYSDB.user_access,

    SYSDB.user_access_desc,

    SYSDB.state,

    SYSDB.state_desc

    FROM #databases AS DB

    INNER JOIN sys.databases AS SYSDB ON DB.DATABASE_NAME = SYSDB.name

    INNER JOIN (SELECT database_id,

    name AS source_file_name_main,

    physical_name AS physical_name_main

    FROM sys.master_files AS SYSMF

    WHERE SYSMF.file_id = 1) AS SYSMFM ON SYSMFM.database_id = SYSDB.database_id

    INNER JOIN (SELECT database_id,

    name AS source_file_name_log,

    physical_name AS physical_name_log

    FROM sys.master_files AS SYSMF

    WHERE SYSMF.file_id = 2) AS SYSMFL ON SYSMFL.database_id = SYSDB.database_id

    WHERE SYSDB.database_id > 4

    ORDER BY DATABASE_SIZE desc;

    DROP TABLE #databases;

    --**************9. Data and log file size***********

    if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')

    drop table #dbsize

    create table #dbsize

    (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))

    go

    insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)

    exec sp_msforeachdb

    'use [?];

    select DB_NAME() AS DbName,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),

    sum(size)/128.0 AS File_Size_MB,

    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,

    SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB

    from sys.database_files  where type=0 group by type'

    go

    -------------------log size--------------------------------------

    if exists (select * from tempdb.sys.all_objects where name like '#logsize%')

    drop table #logsize

    create table #logsize

    (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))

    go

    insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)

    exec sp_msforeachdb

    'use [?];

    select DB_NAME() AS DbName,

    sum(size)/128.0 AS Log_File_Size_MB,

    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,

    SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB

    from sys.database_files  where type=1 group by type'

    go

    --------------------------------database free size

    if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')

    drop table #dbfreesize

    create table #dbfreesize

    (name sysname,

    database_size varchar(50),

    Freespace varchar(50)default (0.00))

    insert into #dbfreesize(name,database_size,Freespace)

    exec sp_msforeachdb

    'use [?];SELECT database_name = db_name()

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')

    ,''unallocated space'' = ltrim(str((

    CASE

    WHEN dbsize >= reservedpages

    THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

    ELSE 0

    END

    ), 15, 2) + '' MB'')

    FROM (

    SELECT dbsize = sum(convert(BIGINT, CASE

    WHEN type = 0

    THEN size

    ELSE 0

    END))

    ,logsize = sum(convert(BIGINT, CASE

    WHEN type <> 0

    THEN size

    ELSE 0

    END))

    FROM sys.database_files

    ) AS files

    ,(

    SELECT reservedpages = sum(a.total_pages)

    ,usedpages = sum(a.used_pages)

    ,pages = sum(CASE

    WHEN it.internal_type IN (

    202

    ,204

    ,211

    ,212

    ,213

    ,214

    ,215

    ,216

    )

    THEN 0

    WHEN a.type <> 1

    THEN a.used_pages

    WHEN p.index_id < 2

    THEN a.data_pages

    ELSE 0

    END)

    FROM sys.partitions p

    INNER JOIN sys.allocation_units a

    ON p.partition_id = a.container_id

    LEFT JOIN sys.internal_tables it

    ON p.object_id = it.object_id

    ) AS partitions'

    -----------------------------------

    if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')

    drop table #alldbstate

    create table #alldbstate

    (dbname sysname,

    DBstatus varchar(55),

    R_model Varchar(30))

    --select * from sys.master_files

    insert into #alldbstate (dbname,DBstatus,R_model)

    select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases

    --select * from #dbsize

    insert into #dbsize(Dbname,dbstatus,Recovery_Model)

    select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'

    insert into #logsize(Dbname)

    select dbname from #alldbstate where DBstatus <> 'online'

    insert into #dbfreesize(name)

    select dbname from #alldbstate where DBstatus <> 'online'

    select

    d.Dbname,d.dbstatus,d.Recovery_Model,

    (file_size_mb + log_file_size_mb) as DBsize,

    d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,

    l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace

    from #dbsize d join #logsize l

    on d.Dbname=l.Dbname join #dbfreesize fs

    on d.Dbname=fs.name

    order by Dbname

    --**********SQL server service account details*************

    select servicename, service_account,last_startup_time from sys.dm_server_services

     

  • Find below script for SQL server configuration and DB details.

    --***********1. SQL Server version deatils**********

    SELECT

    SERVERPROPERTY('servername') AS [Server Name],@@ServiceName as [InstanceName],

    CASE

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'

    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'

    ELSE 'unknown'

    END AS Version,

    SERVERPROPERTY('ProductVersion') AS Number,

    SERVERPROPERTY('Edition') AS Edition,

    SERVERPROPERTY('ProductLevel') AS SP

    --******************2. Database details*************

    Use master

    Go

    select name as [Database Names] from sys.databases

    --******************3. Job Details*************

    GO

    use msdb

    go

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE active_start_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),active_start_date),1,4) + '/' +

    substring(convert(varchar(15),active_start_date),5,2) + '/' +

    substring(convert(varchar(15),active_start_date),7,2)

    END,

    'Start Time' = CASE len(active_start_time)

    WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(active_start_time,3),1)

    +':' + right(active_start_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(active_start_time,5),1)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(active_start_time,6),2)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    END,

    -- active_start_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    +     CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER JOIN dbo.sysjobschedules

    ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time = 0

    UNION

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE next_run_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),next_run_date),1,4) + '/' +

    substring(convert(varchar(15),next_run_date),5,2) + '/' +

    substring(convert(varchar(15),next_run_date),7,2)

    END,

    'Start Time' = CASE len(next_run_time)

    WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(next_run_time,3),1)

    +':' + right(next_run_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(next_run_time,6),2)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    END,

    -- next_run_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    +     CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time <> 0

    ORDER BY [Start Date],[Start Time]

     

    GO

    --******************4. Login details*************

    use master

    GO

    SELECT name AS Login

    FROM sys.server_principals

    WHERE TYPE IN ('U', 'S', 'G')

    and name not like '%##%'

    ORDER BY name, type_desc

     

    --******************5.Min & Max SQL server Memory details*************

    SELECT name, value, value_in_use, [description]

    FROM sys.configurations

    WHERE name like '%server memory%'

    ORDER BY name OPTION (RECOMPILE);

    --******************6. Disk space details*************

    SELECT distinct(volume_mount_point) as Drive,

    total_bytes/1048576 as Size_in_MB,

    available_bytes/1048576 as Free_in_MB,

    (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage

    FROM sys.master_files AS f CROSS APPLY

    sys.dm_os_volume_stats(f.database_id, f.file_id)

    group by volume_mount_point, total_bytes/1048576,

    available_bytes/1048576 order by 1

    --******************7. Database Count*************

    select count(*) as [Database Count] from sys.databases;

    --******************8. Mdf and Ldf file name,size and location*************

    CREATE TABLE #databases

    (

    DATABASE_NAME VARCHAR(50),

    DATABASE_SIZE FLOAT,

    REMARKS VARCHAR(100)

    )

    INSERT #Databases EXEC ('EXEC sp_databases');

    SELECT @@SERVERNAME AS SERVER_NAME,

    DATABASE_NAME,

    SYSMFM.source_file_name_main,

    SYSMFM.physical_name_main,

    SYSMFL.source_file_name_log,

    SYSMFL.physical_name_log,

    DATABASE_SIZE AS '(KB)',

    ROUND(DATABASE_SIZE / 1024, 2) AS '(MB)',

    ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS '(GB)',

    SYSDB.compatibility_level,

    SYSDB.create_date,

    SYSDB.database_id,

    SYSDB.collation_name,

    SYSDB.recovery_model,

    SYSDB.recovery_model_desc,

    SYSDB.user_access,

    SYSDB.user_access_desc,

    SYSDB.state,

    SYSDB.state_desc

    FROM #databases AS DB

    INNER JOIN sys.databases AS SYSDB ON DB.DATABASE_NAME = SYSDB.name

    INNER JOIN (SELECT database_id,

    name AS source_file_name_main,

    physical_name AS physical_name_main

    FROM sys.master_files AS SYSMF

    WHERE SYSMF.file_id = 1) AS SYSMFM ON SYSMFM.database_id = SYSDB.database_id

    INNER JOIN (SELECT database_id,

    name AS source_file_name_log,

    physical_name AS physical_name_log

    FROM sys.master_files AS SYSMF

    WHERE SYSMF.file_id = 2) AS SYSMFL ON SYSMFL.database_id = SYSDB.database_id

    WHERE SYSDB.database_id > 4

    ORDER BY DATABASE_SIZE desc;

    DROP TABLE #databases;

    --**************9. Data and log file size***********

    if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')

    drop table #dbsize

    create table #dbsize

    (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))

    go

    insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)

    exec sp_msforeachdb

    'use [?];

    select DB_NAME() AS DbName,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),

    sum(size)/128.0 AS File_Size_MB,

    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,

    SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB

    from sys.database_files  where type=0 group by type'

    go

    -------------------log size--------------------------------------

    if exists (select * from tempdb.sys.all_objects where name like '#logsize%')

    drop table #logsize

    create table #logsize

    (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))

    go

    insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)

    exec sp_msforeachdb

    'use [?];

    select DB_NAME() AS DbName,

    sum(size)/128.0 AS Log_File_Size_MB,

    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,

    SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB

    from sys.database_files  where type=1 group by type'

    go

    --------------------------------database free size

    if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')

    drop table #dbfreesize

    create table #dbfreesize

    (name sysname,

    database_size varchar(50),

    Freespace varchar(50)default (0.00))

    insert into #dbfreesize(name,database_size,Freespace)

    exec sp_msforeachdb

    'use [?];SELECT database_name = db_name()

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')

    ,''unallocated space'' = ltrim(str((

    CASE

    WHEN dbsize >= reservedpages

    THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

    ELSE 0

    END

    ), 15, 2) + '' MB'')

    FROM (

    SELECT dbsize = sum(convert(BIGINT, CASE

    WHEN type = 0

    THEN size

    ELSE 0

    END))

    ,logsize = sum(convert(BIGINT, CASE

    WHEN type <> 0

    THEN size

    ELSE 0

    END))

    FROM sys.database_files

    ) AS files

    ,(

    SELECT reservedpages = sum(a.total_pages)

    ,usedpages = sum(a.used_pages)

    ,pages = sum(CASE

    WHEN it.internal_type IN (

    202

    ,204

    ,211

    ,212

    ,213

    ,214

    ,215

    ,216

    )

    THEN 0

    WHEN a.type <> 1

    THEN a.used_pages

    WHEN p.index_id < 2

    THEN a.data_pages

    ELSE 0

    END)

    FROM sys.partitions p

    INNER JOIN sys.allocation_units a

    ON p.partition_id = a.container_id

    LEFT JOIN sys.internal_tables it

    ON p.object_id = it.object_id

    ) AS partitions'

    -----------------------------------

    if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')

    drop table #alldbstate

    create table #alldbstate

    (dbname sysname,

    DBstatus varchar(55),

    R_model Varchar(30))

    --select * from sys.master_files

    insert into #alldbstate (dbname,DBstatus,R_model)

    select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases

    --select * from #dbsize

    insert into #dbsize(Dbname,dbstatus,Recovery_Model)

    select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'

    insert into #logsize(Dbname)

    select dbname from #alldbstate where DBstatus <> 'online'

    insert into #dbfreesize(name)

    select dbname from #alldbstate where DBstatus <> 'online'

    select

    d.Dbname,d.dbstatus,d.Recovery_Model,

    (file_size_mb + log_file_size_mb) as DBsize,

    d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,

    l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace

    from #dbsize d join #logsize l

    on d.Dbname=l.Dbname join #dbfreesize fs

    on d.Dbname=fs.name

    order by Dbname

    --**********SQL server service account details*************

    select servicename, service_account,last_startup_time from sys.dm_server_services

     

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply