SQL Security :: File Size And Space Used Is Not Displaying For All Database Other Than Master

Aug 26, 2015

I executed the below query and getting the capacity values only for master database.All other DB shows NULL values for spaceused. I'm actually looking for a query to get all the capacity information other than using temp table and the procedures. Is there any way using SQL query ONLY.

select
db.[dbid] as 'DB ID',
db.[name] as 'Database Name',
af.[name] as 'Logical Name',
convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB, 
convert(decimal(12,2),round(fileproperty(db.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid

Below is the output

DB ID Database Name
Logical Name FileSizeMB
SpaceUsedMB
1 master
master 4.00
3.44
1 master
mastlog 2.00

[Code] ....

View 5 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Log File Size And Log Space Used (Percentage)

Jul 7, 2015

While i execute dbcc sqlperf(logspace); I get following values.

Database NameLog Size (MB)Log Space Used (%)
master 16.17969 13.30275
tempdb 7.429688 61.7245
model 0.7421875 45.78947
msdb 5.554688 25.87904
distribution 2808.93 0.8172179
BANKDB 23438.87 48.20037
WSMIRSDB 109.7422 4.839111

For database BANKDB , Log Space used(%) is 48.83% and Log size is about 23438.87 where as my database size of BANKDB is 60 GB. FULL database and Log back is done every day night one time. My database is performing slow now.

Do we need to take log backup frequently like once a 1 hour so that Log space used will be less. Same query is taking more time to execute than before in same database is it because of log file has increased.

I do index organize and rebuild once a week and stats apply nightly.

Is it correct once log space size is increasing more than 10%. Do we need to take log backup?

View 4 Replies View Related

SQL Security :: Restoring Database Master Key?

Aug 14, 2015

I needed to take a backup of a database and use it on another server and followed the steps in the following article to do so:

[URL]

It restored fine on the destination but I still need to use the original and I get the following error from the application when trying to use it:

create a master key in the database or open the master key in the session before performing this operation.

This is after doing the following to try and restore the master key.

USE <database>;
GO
open
master key decryption by password = '<password>'
RESTORE MASTER KEY
   FROM FILE =
'C:exportedmasterkey'
   DECRYPTION
BY PASSWORD = '<password>'
   ENCRYPTION
BY PASSWORD = '<password>';
GO

After doing this I got the message:

The old and new master keys are identical. No data re-encryption is required.

What else do I need to do?

View 4 Replies View Related

SQL Security :: CREATE DATABASE Permission Denied In Database (Master)

Oct 26, 2015

I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

View 7 Replies View Related

Database Space Size Increased On Deleting Records

Feb 20, 2008

Hi,

I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.

When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?

I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.

Any reasons?

View 6 Replies View Related

Unable To Access The Database Master Under The Current Security Context.

Jun 16, 2007

I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:

The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.

If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?

View 6 Replies View Related

SQL Server 2008 :: How To Update The Database Physical File Location In Master Database

Mar 8, 2015

I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).

ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO

View 1 Replies View Related

The Log File For Database 'Home_alone' Is Full. Back Up The Transaction Log For The Database To Free Up Some Log Space.

Feb 4, 2008



Hi all

Iam working in Prodcution ENV,Please help how make space


The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.

View 10 Replies View Related

Populating Datas From Sql Server Database And Displaying Them In CSV File Format Using C#

Dec 7, 2006

Hi
       I have to populate some datas from sql server database and display it directly in a CSV Format or CSV file. When i run the project it should provide me an option to whether open or save or cancel the file. when i click open it should be opened and be viewed in a excel sheet in CSV format and when i click save it should ask the destination folder and should be saved there in CSV format and when i click cancel it should be cancelled and the application should be closed. pls note that all these actions should happen in the same browser and should not be redirected to anyother page. Can anybody give me the detailed description and code in Asp.Net using C# .It's very Urgent.
RegardsVijay.

View 1 Replies View Related

Restore SQL Master Database From A File

Jul 23, 2005

Hello,I need to restore the Master DB in my SQL 2k server (with sp3). Theproblem is that i don't have SQL backups of this file I only havegeneral backup of the entire c: drive i made using NTBackup. So itried all I know and succesfully run the rebuildm utility. Now when Itry to restore it from the general backup (not SQL backup) using"restore database master from gen_backup" i got error: "The file ondevice 'gen_backup' is not a valid Microsoft Tape Format backup set."RESTORE DATABASE is terminating abnormally.Any ideas how I can restore it?

View 2 Replies View Related

DB Engine :: Recommended Size And File Growth For A Database And Log File?

Sep 22, 2015

What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:

CREATE DATABASE Dummy
ON 
PRIMARY
( NAME = Dummy_data,
    FILENAME = 'D:....DATADummy.mdf',
    SIZE = 250MB,
    FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
    FILENAME = 'D:....DATADummy_log.ldf',
    SIZE = 50MB,
    FILEGROWTH = 5MB ) ;
GO

View 3 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.



Any help with this process?

View 1 Replies View Related

Database File And Disk Space

Dec 3, 2007

Hi All,

One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?

Thanks.

View 2 Replies View Related

Database File And Disk Space

Dec 3, 2007

Hi All,

One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?

Thanks.

View 1 Replies View Related

Recover Master Database Fron Only MDF File

Jul 20, 2005

I have SQL server 2000 which recently crashed. I try to recover it andfound out that the Master database is corrupt. I never backup myserver using inline sql backup nor any third party backup program thataware to SQL server. But I have full system backup of volume c on tape(using NTBackup), which includes old version of Master database. Myquestions are:1. How can I recover this file into SQL server?2. If so, it is not risky to recover Master database from two weeksago?Thanks in advance!

View 1 Replies View Related

Could Not Locate File 'xxx_Log' For Database 'master' In Sys.database_files

Nov 12, 2007

Hi All,

I am truncating Transactionlog€¦ following the steps mentioned in MSKB.. what is the problem?



Use master
Go

Backup log xxx with truncate_only
go
Dbcc shrinkfile(xxx_Log)
Go

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files.
The file either does not exist, or was dropped.


Please help me.

Thanks in advance

View 31 Replies View Related

SSIS: Populate Database From A Space Delimited File

Aug 9, 2007

The problem is that I have (for example) following data

AAAA AA ALH
B BBB MIL
CCCCCC CAC Q
D D P

in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.


P.S. the only way to recognize colums is through spaces b/w the data.

View 5 Replies View Related

The Database File Is Larger Than The Configured Maximum Database Size.

Mar 20, 2007

I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.

Thanks for any help any of you can give!

Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091

View 3 Replies View Related

Database File Is Larger Than The Configured Max Database Size.

Feb 18, 2008

Hello,

I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.

My database size is around 350MB. So when I am trying to synchronize this is the error message that I get.
" The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].

I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.

connstr= "Data Source=Storage CardItems.sdf;Max Database Size=500;"

Any help regarding this would be appreciated.

Thank you
.

View 6 Replies View Related

Database File Size

Jul 20, 2007

I have a Database which when I Right Click and go to Properties size is 52 GB



But the Size of MDF + NDF Files is 25 + 7 = 32 GB. Log file Size is 20 GB. So I am thinking -- Properties Size of DB includes size of Log Files too -- is that correct?



But when I do a Full Backup the .bak File Size is 26 GB -- does the Full Backup Shrink a DB ?



I thot Full Backup only Shrink the Log Files and could not find anywhere in BOL where it says BACKUP shrinks the empty space in Database -- can somebody confirm this?



View 3 Replies View Related

Database Backup File Size

Oct 27, 2004

How does backup database command works? I don't see size of database backup file increasing while backup is in progress OR is it locked till the backup is finished.

Thanks.

View 1 Replies View Related

How To Reduce Database File Size.

Mar 9, 2005

We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs.

We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space.

What is the best way to get them a copy of the database while reducing the footprint?

View 6 Replies View Related

Database File Size Question Please

Aug 28, 2005

Hi,I have set the DB to auto grow by 30 %. As well I have set it tounrestricted size.... However , I see the available size continually beingreduced to now less then .54 MB... Why is there not enough available ?G

View 3 Replies View Related

Database File Size Problem

Apr 20, 2007

Hi,

I am facing problem of memory size of database file.



I am using SQL Server Express Edition for my application which will log point values after every second.

I have two table in my database

PointValues1_500 with the following columns

DateAndTime of type DATETIME with cluster index
Val1, Val2, Val3 ----- Val500 of type NUMERIC(18,6)

PointValues501-1000 with following columns


DateAndTime of type DATETIME with cluster index

Val1, Val2, Val3 ----- Val500 of type NUMERIC(18,6)

According to specification provided by microsoft DATETIME takes 8 bytes and NUMERIC(18,6) takes 9 bytes and 132 bytes per row for internal usage. So, it will take 4710 bytes approximately per row including index (if i am right).



When I put 60 rows in each table data file size is increased by 1 MB which should be 4710 * 60 (rows) * 2(tables) = 0.6 MB approx. I this scenario i am losing 0.4 MB memory that will cost me a lot if i have point values for 1 hour = 3600 rows per table.



Please give me a suggestion how to overcome this lost.



Best Regards

Haseeb Ahmad

View 2 Replies View Related

How To Decrease The Database File Size

Jul 19, 2007

Hi all,



How to derease the database file size in Primary filegroup.



Thanks,

View 5 Replies View Related

T-SQL (SS2K8) :: Initial Size Of Database File

Apr 9, 2013

How to get the initial size of the database file using T-SQL.

sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.

View 4 Replies View Related

SQL 2012 :: Database Size From Backup File

Apr 21, 2014

finding the database size from the backup file.I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?

One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?

View 5 Replies View Related

T-SQL (SS2K8) :: Getting Files Size For Each Database File?

Oct 28, 2014

I would like to add to it the actual file size in mb or gb of each file to the results.

select sd.name,mf.name as logical_name,mf.physical_name,
case
when dm.mirroring_state is null then 'No'
else 'Yes'
end as Mirrored
from sys.sysdatabases sd JOIN
sys.master_files mf on sd.dbid = mf.database_id
join sys.database_mirroring dm on sd.dbid = dm.database_id

The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?

View 2 Replies View Related

Size Of MASTER & MSDB

Oct 13, 1999

We're just in the process of moving to 7.0, although SQL 7 has the autogrow feature we'd prefer to size MASTER and MSDB properly in the first place to avoid disk fragmentation. Does anyone have an experience/suggestions as to appropriate sizes, particulary for MASTER?

View 5 Replies View Related

SQL Security :: Can't Open Database File Access Denied

Aug 17, 2015

Event 17204, FCB::Open failed: Could not open file F:MSSQLDatafilename.mdf for file number 1.  OS error: 5(Access is denied.).When I look at the file permissions of filename.mdf, there is no MSSQLSERVER group permissions listed nor can I add it. I have tried to add MSSQLSERVER and NT SERVICEMS SQL $MSSQLSERVER but neither exist. There is also a ReportServer.mdf in the same folder with MSSQLSERVER permissions!Did I somehow delete this group? What can I do to restore this permission?

View 6 Replies View Related

SQL Server 2000 Database Back Up File Size

Sep 27, 2004

I am having a bit of a mare on the database backup front - i.e. monster
DB, not a great deal of space to back it up into.

As I do the backup, the file on the disk seems to stay at 2KB

How does the file get written? Is there a temp somewhere first?


Cheers in advance - Dave

View 1 Replies View Related

SQL 2012 :: Increase File Size For A Mirrored Database?

Apr 14, 2015

I need to increase the file size for a mirrored database. I am new to using mirroring for replication. Will increasing the file size break the mirror?

View 2 Replies View Related

Stroing Large Size File In Sql Server Database.

Apr 20, 2007

Hi
I want to store large files like pdf file,Html page,audio file in Sql Server database.How can i do it?
if somebody know then tell me as soon as possible.
Thanks in advance.
Bye

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved