I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?
DB is generally small. ~25 tables, 100 SPs, 10 views, etc.
Note:
I have 4 queues using SQL Notifications, but when selecting from them results in no data.
Thanks
Scott
Hi
I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..
When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being
"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"
Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..
Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?
Thanks in Advance
KI
This is a question that has always intrigued me: what is the ideal File Allocation Unit Size for a disk holding only data or index pages on a server running SQL Server? It seems to me that 8,192 would be the ideal size as it would enable the system to gobble up an entire page in one go. Any ideas?
View 18 Replies View RelatedHi
I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..
When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being
"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"
Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..
Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?
Sorry Had I started this topic in the wrong forum.
Thanks in Advance
KI
I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?
Thanks,
Michael
The following procedure will display the size of all the user tables in a database.
CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end
calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)
declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end
select * from #SpaceUsed
drop table #SpaceUsed
deallocate c1
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
Any help would be greatly appreciated
Kind Regards
Carel Greaves
Just wondering if you could help me on this one. I'm not sure if mytransaction logs are behaving oddly or what. I've successfuly managedto shrink my transaction logs from 7GB down to 1MB and now I find itstrange that the log file doesn't seem to increase its size. Thetimestamp of the logfile is updating as well. But the size of the fileis constant. I haven't configured my database to do auto-shrink so Imreally confused why it hasn't changed its size for more than a monthnow.Hope I'm not losing any data here.Kindly advise.
View 7 Replies View RelatedI have 57 tables, 7 views and 1 stored procedure. Just wanted know based on these requirements how can I find the size of the database. Though the DB contains lots of tables, views and procedures. I am moving these details to new DB server. So I need to put right requirements.
View 2 Replies View RelatedI need to build a query to calculate the size of all tables in a database ...
View 5 Replies View RelatedI have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.
/*
Original by: Bill Graziano (SQLTeam.com)
Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40)
*/
declare @low int
select @low = low from
master.dbo.spt_values
where number = 1 and type = 'E'
select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved,
(d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp,
((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused
from
sysobjects o
inner join
(select distinct id, rowcnt
from sysindexes
where keys is not null and first != 0) ro on o.id = ro.id
inner join
(select id, sum(reserved) reserved
from sysindexes
where indid in (0, 1, 255)
group by id) r on o.id = r.id
inner join
(select c.id, dpages+isnull(used, 0) data from
(select id, sum(dpages) dpages
from sysindexes
where indid < 2
group by id) c full outer join
(select id, isnull(sum(used), 0) used
from sysindexes
where indid = 255
group by id) t on c.id = t.id) d on r.id = d.id
inner join
(select id, sum(used) used
from sysindexes
where indid in (0, 1, 255)
group by id) i on d.id = i.id
where o.xtype = 'U'
order by reserved desc
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,
View 1 Replies View RelatedI found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
Good day. I am a non-programmer but one tasked to "check" on the recent error in our company's systems.
In summary, most, if not all Tables were deleted from the different databases. Add to that, the logs prior to the event were also lost or deleted. Yes we do have back ups but restoring wasn't that simple. Another domain server was corrupted causing further delays in the restoration.
This is the second time it has happened and the events were 31 days apart. We would like to identify the causes for our preventive actions. Is there a hardware/software problem that may have caused these? Of course, we would like to rule out that it was done intentionally... but if you guys think otherwise, i'd like to further ask how we can validate the theory.
Thank you very much...
Inzo
Greetings,
I got severe data corruption issue today when I copied a SQLServer2000 database from our remote server to my local PC through enterprise manager. The new copy on my local PC did not have any records in the user tables, and when I looked at the remote server, the records gone either with only table skeletons and procedures there. The mdf file on the remote server is emptied at all, but that on my local PC still has 50MB. I think there are data in this file, but the ldf log was also emptied, but I cannot recover it -- I do not have backup and the ldf file was also emptied.
I ran checkdb, and it did not tell any error but show that all the user defines tables has zero records.
I wonder whether there is now any way to recover the data. They are important and I really dont want to lose them.
Thanks in advance!
OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.Â
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
Hi Gurus,i am having problems with restoring a ms sql database.i have restored the database using veritas to a different location('g:datafiles') in no recover mode.when i view the database through the Enterprise Manager, it shows thedatabase as silver icon (loading).i go to sql analyzer, and put in the restore commandrestore log myDatabasefrom 'mylog'with recoverythis produces an errorRESTORE FILELISTONLYFROM jobsServer: Msg 3206, Level 16, State 1, Line 1No entry in sysdevices for backup device 'mylog'. Update sysdevicesand rerunstatement.i look in master..sysdevices - no entry for mylog, but then no entriesfor the log files for any of the other perfectly working databaseseither.i do have a copy of the log files ('c:logfiles') in another locationi would like the following help if possible:* a way to update sysdevices with the log file i wish to apply to myrestored database so it will let my restore go through properly.* a way to specify to use apply the logs in 'c:logfiles' withouthaving to give the restore statement a logical name for the log files(which naturally won't be in sysdevices!)pls supply transact sqlmy thanksEdwina63(if wish to email please remove h from edwinah@)p.s sp_add_log_file_recover_suspect_db will not work in a partiallyrestored database
View 6 Replies View Related I have a set of disks allocated for a high performance SQL implementation that will entail lots of large queries. My question is do I allocate more IOPS to the logs or to the database?
For example, if I have a 10 disk RAID 1/0 and a 4 disk RAID 1/0 available, which do I allocate to the logs and which do I allocate to the database? Which will require the most IOPS?
Thanks!
Hi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil
Hi,
I've got a sitatuation where one of our sql databases appears to be frequently "starting". The log entry looks like:
Starting up database 'Database'.
And seems to occur at irregular intervals and does not seem to be inline with any other db activity....i.e. transaction log backups, insertions or reads.
This DB is fairly busy receving inserts from our PBX CTI software almost constantly.
Note this is the only DB on the server displaying this behaviour (we've got two named instances running with several databases in each).
Any help / pointers appreciated.
Rgds,
SLE
Hi,
I need to write a reporting app that process daily IIS logs that are averaging about 299MB a day!
I am considering using SSIS to put the log data into a sql server 2005 database.
Has anyone done this? Or does anyone know if it is possible? And without using the MS LogParser?
Thanks in advance and any help greatly appreciated!
Regards,
kitquo2.
I am finding that the size of my SQL Server 7 tables are approx 75% LARGER than the same Visual FoxPro table. I am not considering indexes in either one. The only difference is that the SQL Server table has a timestamp that the Visual FoxPro table does not.
Is this normal? or did I do something wacko?
I upsized the data from Visual Fox to SQL by appending the info in the Visual Fox table into a remote view and then doing a TABLEUPDATE to send it to SQL Server.
Any help would be appreciated.
Rick
I'm having to do some estimation on the size of the db that I will eventually create.
I have a 6.7 million row table that holds 5 integers and a varchar(8).
Therefore my crude maths are that an integer is 4 bytes and a varchar is max 8 and therefore each line is 28 maxmimum bytes long. Multiply by 6.7 million and you get 1876000000 bytes or 183203KB or 179MB's.
With me playing with this table the db has balooned from 23mb to 1,325mb. This doesnt work out. One possible explanation is if SQL Server does the same as Access and you need to compress to gain the space back. I wasn't aware of this though. I have filled and emptied the table several times over to test the import routine.
I therefore have a few questions.
1. Why has the db ballooned to this size?
2. How do I accurately work out future db/table sizes?
3. Is there a way to work out the size of a table?
4. Do SQL db's have to be compacted like Access's?
Help appreciated.
Hi,
I know I can check individual table's properties for the size and row count of each, but is there a way of doing it for all tables within a database in SQL server 2005 ? I have about 69 tables in one..
Thanks,
Raddy
Hi,
I have a database which is 25 GB in size. The database has few tables.
Can I individually find out which table is occupying more space?
Thanks
Santhosh
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?
I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:
RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO
The transaction logs have never been truncated.
But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.
thanks.
Is there an easy way to monitor (audit) who logs onto a database ??Thanks for any and all help that is provided.Art
View 2 Replies View RelatedHello.
Not an SQL admin. We have an SQL 2005 server that has about 5 DB's on it. One database is maintained primarily by a third party. Often when they need to do upgrades they login remotely to the desktop of our SQL server. Is there a way to apply permissions to specific databases like you would for NTFS? That way they can only backup their database and not do anything to any other databases? Thanks.
Is it possible to determine the data size of records in a table. basically I would like to run a select query with a condition and the result will be xKB. I know that this is possible on a database level, but can it be done on a record level (or number of records).
Cheers
H